/*
 * 文件名：UserDaoImp.java
 * 版权：Copyright by www.wootide.com
 * 描述：
 * 修改人：ozm
 * 修改时间：2013-1-24
 * 跟踪单号：
 * 修改单号：
 * 修改内容：
 */
package com.wootide.cms.service.facility.room.roominfo.dao.imp;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.wootide.cms.common.Constants;
import com.wootide.cms.common.QueryCondition;
import com.wootide.cms.common.QueryResults;
import com.wootide.cms.common.util.CommonUtil;
import com.wootide.cms.log.syslog.RunLog;
import com.wootide.cms.service.facility.room.roominfo.dao.RoomInfoDao;
import com.wootide.cms.service.facility.room.roominfo.pojo.RoomInfo;
import com.wootide.cms.service.facility.room.table.pojo.TableNoInfo;


public class RoomInfoDaoImp extends HibernateDaoSupport implements RoomInfoDao
{
    
    private RunLog runLog;
    
    @Override
    public RoomInfo findRoomInfoById(Integer roomId)
    {
        runLog.debug("roomId=" + roomId);
        
        return getHibernateTemplate().get(RoomInfo.class, roomId);
    }
    
   
    
    @Override
    public int findNumByRoomInfoNum(String roomNum)
    {
        try
        {
            runLog.debug("roomName=" + roomNum);
            
            return ((Number)getHibernateTemplate().findByNamedQuery("findRoomInfoNumByNum",
                    roomNum)
                    .get(0)).intValue();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return 0;
        
    }
    /**
     * 查询全部可预订房间
     * Description: <br>
     * Implement: <br>
     * @param queryConditon
     * @return
     * @see
     * @return QueryResults
     * @author ozm2013-3-11下午3:28:09
     */
    public QueryResults queryBookableRoomForPages(QueryCondition queryConditon)
    {
        runLog.debug("queryConditon=" + queryConditon);
        Object[] args = {(Timestamp)queryConditon.getConditionValueList().get(0)
                ,(Timestamp)queryConditon.getConditionValueList().get(1)
                ,(Timestamp)queryConditon.getConditionValueList().get(0)
                ,(Timestamp)queryConditon.getConditionValueList().get(1)};
        String sql = "from RoomInfo r where r.roomId in (select p.roomInfo.roomId from RoomPeriod p  where p.status = 0 and  p.startTime >= '" +
                args[0] + "'  and p.endTime <= '" + args[1] + 
                		"' ) or r.innerIndex in  ( select t.roomInfo.roomId from TableNoInfo t where  t.innerIndex in (select d.tableNoInfo.innerIndex from TablePeriod d where d.status = 0  and d.startTime >= '" +
                		args[2]+ "' and d.endTime <= '" + args[3] + 
                				"' ))";
//        String sql = "from RoomInfo r where r.roomId in (select p.roomInfo.roomId from RoomPeriod p  where p.status = 0 and  p.startTime >= ? and p.endTime <= ?) or r.roomId in  ( select t.roomInfo.roomId from TableNoInfo t where  t.tableId in (select d.tableNoInfo.tableId from TablePeriod d where d.status = 0  and d.startTime >= ? and d.endTime <= ? ))";
        @SuppressWarnings("unchecked")
        List<Object> queryResults = getHibernateTemplate().find(sql);
        QueryResults result = new QueryResults();
        result.setResults(queryResults);
        return result;
    }
    
    /**
     * 查询房间的可预订时段
     * Description: <br>
     * Implement: <br>
     * @param queryConditon查询条件
     * @return
     * @see
     * @return QueryResults结果
     * @author ozm2013-3-14下午5:20:50
     */
    @SuppressWarnings({"rawtypes"})
    public List queryBookableRoomPeriods(QueryCondition queryConditon)
    {
        runLog.debug("queryConditon=" + queryConditon);
        List  conditionValues =  queryConditon.getConditionValueList();
        String sql = "from RoomPeriod p  where  ";
        //前置条件
        boolean hasPreCondition = false;
        
        if (CommonUtil.stringExist((String)(conditionValues.get(0))))
        {   
            sql = sql + "p.roomInfo.roomNum = '"  + conditionValues.get(0) + "'";
            hasPreCondition = true;
        }
       
        if(null != conditionValues.get(1))
        {   
            sql = CommonUtil.makeSQL(sql, hasPreCondition);
            sql =  sql + "  p.roomInfo.roomTypeInfo.roomTypeId = " + conditionValues.get(1) ;
            hasPreCondition = true;
        }
        
        if(null != conditionValues.get(2))
        {
            sql = CommonUtil.makeSQL(sql, hasPreCondition);
            sql = sql + "  p.status = 0 and  p.startTime >= '" + conditionValues.get(2)  +  "'"  ;
            hasPreCondition = true;
        }
         
        if(null != conditionValues.get(3))
        {
            sql = CommonUtil.makeSQL(sql, hasPreCondition);
            
            sql = sql + " p.endTime <= '"+ conditionValues.get(3) + "'";
        }
        	
      
        return getHibernateTemplate().find(sql);
    }
    
    
    /**
     * 
     * Description: 通过房间类型ID获取房间信息
     * Implement: <br>
     * @param queryConditon 房间 查询条件
     * @see
     * @return List 要获取的房间信息列表
     * @author ozm2013-1-30上午10:21:23
     */
    @SuppressWarnings("rawtypes")
    public List findBookableRoomsByConditions(QueryCondition queryConditon)
    {
        runLog.debug("queryConditon=" + queryConditon);
        List  conditionValues =  queryConditon.getConditionValueList();
        String sql = "from RoomInfo r where";
        //前置条件
        
        if(!"".equals(conditionValues.get(0)))
        {
            sql +="r.roomNum = '" + conditionValues.get(0) + "' and";
            
        }
        sql  = sql +"  r.roomTypeInfo.roomTypeId = " + conditionValues.get(1)
                +" and r.innerIndex in";
        
        if (Constants.RooomType.CHESS_ROOM.equals(conditionValues.get(1))  || Constants.RooomType.CHABA.equals(conditionValues.get(1)))
        {
            sql+= " (select t.roomInfo.innerIndex from TableNoInfo t  where t.innerIndex in (select p.tableNoInfo.innerIndex from TablePeriod p where p.status = 0 and  p.startTime >= '"
                    + conditionValues.get(2) + "'  and p.endTime <= '"+ conditionValues.get(3) + "')) ";
        }
        else
        {
            sql+= " (select p.roomInfo.innerIndex from RoomPeriod p  where p.status = 0 and  p.startTime >= '" 
                    + conditionValues.get(2) + "'  and p.endTime <= '"+ conditionValues.get(3) + "'))";
        }
                
        runLog.debug("findRoomsByConditions sql=" + sql);
        return getHibernateTemplate().find(sql);
               
    }
    
    
    /**
     * 查询可预订桌次
     * Description: <br>
     * Implement: <br>
     * @param queryConditon查询条件
     * @return
     * @see
     * @return List结果
     * @author ozm2013-3-14下午5:20:50
     */
    @SuppressWarnings("rawtypes")
    public List queryBookableTableNos(QueryCondition queryConditon)
    {
        runLog.debug("queryConditon=" + queryConditon);
        List  conditionValues =  queryConditon.getConditionValueList();
        String sql = "from TableNoInfo t where ";
        if(!"".equals(conditionValues.get(0)))
        {
            sql +=" t.roomInfo.roomNum = '" + conditionValues.get(0) + "' and";
        }
        sql  = sql
                +"  t.innerIndex in (select p.tableNoInfo.innerIndex from TablePeriod p  where p.status = 0 and  p.startTime >= '" 
                + conditionValues.get(2) + "'  and p.endTime <= '"+ conditionValues.get(3) + "')";;
        runLog.debug("findRoomsByConditions sql=" + sql);
        return getHibernateTemplate().find(sql);
    }
    
    /**
     * 查询桌次的可预订时段
     * Description: <br>
     * Implement: <br>
     * @param queryConditon查询条件
     * @return
     * @see
     * @return QueryResults结果
     * @author ozm2013-3-14下午5:20:50
     */
    @SuppressWarnings({"rawtypes"})
    public List queryBookableTablePeriods(QueryCondition queryConditon)
    {
        runLog.debug("queryConditon=" + queryConditon);
        List  conditionValues =  queryConditon.getConditionValueList();
        String sql = "from TablePeriod p  where  ";
        //前置条件
        boolean hasPreCondition = false;
        
        if(!"".equals(conditionValues.get(0)))
        {
           
            sql +="p.tableNoIn.roomInfo.innerIndex = " + conditionValues.get(0);
            hasPreCondition = true;
        }
        if (!"".equals(conditionValues.get(1)))
        {
            sql = CommonUtil.makeSQL(sql, hasPreCondition);
            sql = sql + "p.tableNoInfo.tableNo = '"  + conditionValues.get(1) + "'" ;
            hasPreCondition = true;
        }
       
        
        if(null != conditionValues.get(2))
        {
            sql = CommonUtil.makeSQL(sql, hasPreCondition);
            sql = sql + "  p.status = 0 and  p.startTime >= '" + conditionValues.get(2)  +  "'";
            hasPreCondition = true;
        }
         
        if(null != conditionValues.get(3))
        {
            sql = CommonUtil.makeSQL(sql, hasPreCondition);
            sql = sql + " p.endTime <= '"+ conditionValues.get(3) + "'";
        }
            
      
        return getHibernateTemplate().find(sql);
    }
    
    /**
     * 查询出预订预览界面预订时段的所有房间
     * Description: <br>
     * Implement: <br>
     * @param queryConditon 时段表中的索引条件
     * @return
     * @see
     * @return List 被预定时段的的房间
     * @author ozm2013-3-27下午5:42:30
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public List<RoomInfo> queryBookingPreviewRoomsByPeriods(final QueryCondition queryConditon)
    {
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = " from RoomInfo r where ";
                
                if (!CommonUtil.collectionIsNull((Set)queryConditon.getConditionValueList().get(0)))
                {
                    hql = hql
                            + "r.innerIndex in (select p.roomInfo.innerIndex from RoomPeriod p  where p.innerIndex in (:roomPeriods) ) ";
                    
                    if (!CommonUtil.collectionIsNull((Set)queryConditon.getConditionValueList().get(1)) )
                    {
                        hql = hql + " or ";
                    }
                }
                
                if (!CommonUtil.collectionIsNull((Set)queryConditon.getConditionValueList().get(1)) )
                {
                    hql = hql
                            + "r.innerIndex in (select a.tableNoInfo.roomInfo.innerIndex from TablePeriod a  where a.innerIndex in (:tablePeriods) )";
                }
                
                Query query = getSession().createQuery(hql);
                if (!CommonUtil.collectionIsNull((Set)queryConditon.getConditionValueList().get(0)))
                {
                    query.setParameterList("roomPeriods",
                            new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(0))));
                }
                
                if (!CommonUtil.collectionIsNull((Set)queryConditon.getConditionValueList().get(1)) )
                {
                    query.setParameterList("tablePeriods",
                            new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(1))));
                }
                
                return query.list();
            }
        });
       
    }
    
    
//    public List queryBookingPreviewRoomByPeriods(QueryCondition queryConditon)
//    {
//        Object[] values = {queryConditon.getConditionValueList().get(0),queryConditon.getConditionValueList().get(1)};
//        String sql = " from RoomInfo r where r.innerIndex in (select p.roomInfo.innerIndex from RoomPeriod p  where p.innerIndex in (?) ) or r.innerIndex in (select a.tableNoIn.roomInfo.innerIndex from TablePeriod a  where a.innerIndex in (?) )" ;
////        Query query = getSession().createQuery(sql);  
////        query.setParameterList("alist", a);  
//        return  getHibernateTemplate().find(sql, values);
//    }
    
    
   
    /**
     * 查询订单预览界面房间时段信息
     * Description: <br>
     * Implement: <br>
     * @param queryConditon 查询条件
     * @return
     * @see
     * @return List 房间时段信息集合
     * @author ozm2013-3-27下午6:18:08
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public List queryBookingPreviewRoomPeriodsByIds(final QueryCondition queryConditon)
    {
//        Object[] values = {queryConditon.getConditionValueList().get(2),queryConditon.getConditionValueList().get(0)};
//        String hql = "from RoomPeriod p where p.roomInfo.innerIndex = ? and p.innerIndex in (?)";
        
        
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = "from RoomPeriod p where p.roomInfo.innerIndex = " + queryConditon.getConditionValueList().get(3) + " and p.innerIndex in (:roomPeriods) order by  p.innerIndex asc";
                
                Query query = getSession().createQuery(hql);
//                if (null != queryConditon.getConditionValueList().get(0))
//                {
                    query.setParameterList("roomPeriods",
                            new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(0))));
//                }

                return query.list();
            }
        });
    }

    /**
     * 查询订单预览界面桌次信息
     * Description: <br>
     * Implement: <br>
     * @param queryConditon 查询条件
     * @return
     * @see
     * @return List 桌次信息集合
     * @author ozm2013-3-27下午6:18:08
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public List<TableNoInfo> queryBookingPreviewTableNosByPeriods(final QueryCondition queryConditon)
    {
//        String hql = "from TableNoInfo t where  t.roomInfo.innerIndex = " + queryConditon.getConditionValueList().get(2)
//                +" and t.innerIndex in(select p.tableNoInfo.innerIndex from TablePeriod p where p.innerIndex in (?)) ";
        
//        return getHibernateTemplate().find(hql, queryConditon.getConditionValueList().get(1));
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = "from TableNoInfo t where  t.roomInfo.innerIndex = " + queryConditon.getConditionValueList().get(3)
                        +" and t.innerIndex in(select p.tableNoInfo.innerIndex from TablePeriod p where p.innerIndex in (:tablePeriods)) ";
                
                Query query = getSession().createQuery(hql);
//                if (null != queryConditon.getConditionValueList().get(1))
//                {
                    query.setParameterList("tablePeriods",
                            new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(1))));
//                }

                return query.list();
            }
        });
    }

    /**
     * 查询订单预览界面桌次时段信息
     * Description: <br>
     * Implement: <br>
     * @param queryConditon 查询条件
     * @return
     * @see
     * @return List 桌次时段集合
     * @author ozm2013-3-27下午6:18:08
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public List queryBookingPreviewTablePeriodsByIds(final QueryCondition queryConditon)
    {
//        String hql = "from TablePeriod p where  p.tableNoInfo.innerIndex = " + queryConditon.getConditionValueList().get(3)
//                +" and p.innerIndex in (?) ";
        
//        return getHibernateTemplate().find(hql, queryConditon.getConditionValueList().get(1));
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = "from TablePeriod p where  p.tableNoInfo.innerIndex = " + queryConditon.getConditionValueList().get(4)
                        +" and p.innerIndex in (:tablePeriods) order by  p.innerIndex asc";
                
                Query query = getSession().createQuery(hql);
//                if (null != queryConditon.getConditionValueList().get(1))
//                {
                    query.setParameterList("tablePeriods",
                            new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(1))));
//                }

                return query.list();
            }
        });
    }

    
    /**
     * 查询订单预览时间段信息
     * Description: <br>
     * Implement: <br>
     * @return
     * @see
     * @return List
     * @author ozm2013-3-30下午11:59:12
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public List queryPreviewPeriods(final QueryCondition queryConditon)
    {
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            
            
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = "";
                Query query = null;
                if (CommonUtil.stringExist((String)queryConditon.getConditionValueList().get(2)))
                {
                    hql = hql + "from RoomPeriod p where  p.roomInfo.roomNum = '" + queryConditon.getConditionValueList().get(2)
                    +"' and p.innerIndex in (:roomPeriods) ";
                    query = getSession().createQuery(hql);
                    
                    if (!CommonUtil.collectionIsNull((Set)queryConditon.getConditionValueList().get(0)))
                    {
                        query.setParameterList("roomPeriods",
                                new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(0))));
                    }
                }
                else
                {
                    hql = hql + "from TablePeriod p where  p.tableNoInfo.tableNo = '" + queryConditon.getConditionValueList().get(3)
                    +"' and p.innerIndex in (:tablePeriods) ";
                    
                     query = getSession().createQuery(hql);
                   
                    if (!CommonUtil.collectionIsNull((Set)queryConditon.getConditionValueList().get(1)) )
                    {
                        query.setParameterList("tablePeriods",
                                new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(1))));
                    }
                }
                
                
//                Query query = getSession().createQuery(hql);
//                
//                if (null != queryConditon.getConditionValueList().get(0))
//                {
//                    query.setParameterList("roomPeriods",
//                            new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(0))));
//                }
//
//                else if (null != queryConditon.getConditionValueList().get(1))
//                {
//                    query.setParameterList("tablePeriods",
//                            new ArrayList((Set<Integer>)(queryConditon.getConditionValueList().get(1))));
//                }
                
                return query.list();
            }
        });
    }
   
    
    
    public RunLog getRunLog()
    {
        return runLog;
    }
    
    public void setRunLog(RunLog runLog)
    {
        this.runLog = runLog;
    }

    /**
     * 通过索引查询房间时段或者桌次时段
     * Description: <br>
     * Implement: <br>
     * @param idList 索引列表
     * @param type  查询类型
     * @return
     * @see
     * @return List
     * @author ozm2013-4-1下午2:28:13
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public List queryPeriodsByIdList(final List idList,final int type)
    {
        
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = "";
                Query query = null;
                if (Constants.PeriodType.ROOM_PERIOD == type)
                {
                    hql = "from RoomPeriod p where p.innerIndex in (:roomPeriods) ";
                    query = getSession().createQuery(hql);
                    query.setParameterList("roomPeriods",idList);
                }
                if (Constants.PeriodType.TABLE_PERIOD == type)
                {
                    hql = hql + "from TablePeriod p where  p.innerIndex in (:tablePeriods) ";
                    
                     query = getSession().createQuery(hql);
                     query.setParameterList("tablePeriods",idList);
                 
                }

                return query.list();
            }
        });
    }

  

    /**
     * 通过订单索引获取订单预订的房间记录
     * Description: <br>
     * Implement: <br>
     * @param orderId  订单ID
     * @return
     * @see
     * @return List 结果集
     * @author ozm2013-4-7下午4:12:13
     */
    public List<?> queryOrderRoomsByOrderId(Integer orderId)
    {
        runLog.debug("orderId" + orderId);
        return this.getHibernateTemplate().findByNamedQuery("findRoomPeriodRoomByBookOrderInfoId", orderId);
    }
    
    /**
     * 通过订单索引获取订单预订的时间列表
     * Description: <br>
     * Implement: <br>
     * @param orderId  订单ID
     * @param roomId   房间ID
     * @return
     * @see
     * @return List 结果皆
     * @author ozm2013-4-7下午4:26:25
     */
    public List<?> queryRoomPeriodByOrderIdAndRoomId(Integer orderId,Integer roomId)
    {
        runLog.debug("orderId" + orderId);
        runLog.debug("roomId" + roomId);
        Object[]  values = {orderId,roomId};
        return this.getHibernateTemplate().findByNamedQuery("findRoomPeriodByBookOrderInfoIdAndRoomId", values);
    }

    /**
     * 通过房间ID获取房间信息
     * Description: <br>
     * Implement: <br>
     * @param roomId 房间ID
     * @return
     * @see
     * @return RoomInfo 房间信息
     * @author ozm2013-4-11下午3:02:45
     */
    public RoomInfo findOneRoomInfoById(Integer roomId)
    {
        runLog.debug("roomId=" + roomId);
        
        return this.getHibernateTemplate().get(RoomInfo.class, roomId);
    }

    /**
     * 通过起止时间和房间信息查询时段信息
     * Description: <br>
     * Implement: <br>
     * @param startTime 起始时间
     * @param endTime  结束时间
     * @param roomId   房间ID
     * @return
     * @see
     * @return List<?> 时段信息
     * @author ozm2013-4-11下午5:50:16
     */
    public List<?> queryPeriodsByRoomIdAndStartTimeAndEndTime(Object startTime,
            Object endTime, Object roomId)
    {
        runLog.debug("startTime=" + startTime);
        runLog.debug("endTime=" + endTime);
        runLog.debug("roomId=" + roomId);
        
//        Object[] values = {roomId,startTime,endTime};
        String hql = "from RoomPeriod p where  p.roomInfo.innerIndex = " + roomId +" and p.startTime >= '" + startTime + "'  and  p.endTime <= '" +  endTime + "'";
        return this.getHibernateTemplate().find(hql);
    }

    /**
     * 通过索引列表和订单ID获取房间时段记录
     * Description: <br>
     * Implement: <br>
     * @param orderId 订单索引
     * @param innerIndexs 房间时段索引集合
     * @return
     * @see
     * @return List<?> 结果集合
     * @author ozm2013-4-12下午5:53:42
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public List<?> queryRoomPeriodsInIdListByOrderId(final Integer orderId,
            final Integer[] innerIndexs)
    {
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = "";
                Query query = null;
                hql = "from RoomPeriod p where  p.innerIndex in (:innerIndexs) ";
//                hql = "from RoomPeriod p where p.bookOrderInfo.innerIndex != "+ orderId + " and p.innerIndex in (:innerIndexs) ";
                query = getSession().createQuery(hql);
                query.setParameterList("innerIndexs",innerIndexs);
                return query.list();
            }
        });
    }

    /**
     * 通过索引列表和订单ID获取被取消的房间时段记录
     * Description: <br>
     * Implement: <br>
     * @param orderId 订单索引
     * @param innerIndexs 房间时段索引集合
     * @return
     * @see
     * @return List<?> 结果集
     * @author ozm2013-4-12下午5:54:55
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public List<?> queryRoomPeriodsNotInIdListByOrderId(final Integer orderId,
            final Integer[] innerIndexs)
    {
        return getHibernateTemplate().execute(new HibernateCallback()
        {
            public List doInHibernate(Session session)
                    throws HibernateException, SQLException
            {
                
                String hql = "";
                Query query = null;
                hql = "from RoomPeriod p where p.bookOrderInfo.innerIndex = "+ orderId + " and p.innerIndex not in (:innerIndexs) ";
                query = getSession().createQuery(hql);
                query.setParameterList("innerIndexs",innerIndexs);
                return query.list();
            }
        });
    }

  
   
}
